<template>
  <div></div>
</template>

<script setup lang="ts">
  import * as sqlite3 from 'sqlite3'
  import { resolve } from 'path'
  import fs from 'fs-extra'
  import { app } from '@electron/remote'
  function resolvePath(fileName: string) {
    // return resolve(app.getPath('userData'), 'db', fileName)
    return resolve(process.cwd(), 'db', fileName)
  }

  type User = {
    id: number
    username: string
    age: number
    userType: string
    email: string
    sort: number
  }

  // 连接数据库
  // 执行 verbose 函数，便于调试代码，代码出错，会定位到具体的代码
  const sqlite = sqlite3.verbose()
  // 指定文件路径
  let dbPath = resolvePath('db.db')
  // 指定文件不存在则创建，存在就不进行操作
  fs.ensureFileSync(dbPath)
  // 初始化数据库，指定数据库存储路径，指定数据库操作模式为速写模式
  const db = new sqlite.Database(dbPath, sqlite.OPEN_READWRITE, async (err) => {
    if (err) return console.log(err)
    await initUserTable()
  })

  // // 创建 users 表
  function initUserTable() {
    return new Promise((resolve, reject) => {
      db.run(
        `CREATE TABLE IF NOT EXISTS user (
         id 				INTEGER 			NOT NULL		PRIMARY KEY AUTOINCREMENT,
         username 	CHAR ( 45 ) 	NOT NULL,
         age        INT						NOT NULL,
         userType 	CHAR ( 45 ) 	NOT NULL 		DEFAULT 'user',
         email      CHAR(45)			NOT NULL  	UNIQUE,
         sort 		 	INT 					NOT NULL 		DEFAULT 10
       )`,
        (err) => {
          if (err) return reject(err)
          resolve('创建 users 表成功')
        }
      )
    })
  }

  // 添加数据
  function addUser(user: Omit<User, 'id'>) {
    return new Promise((resolve, reject) => {
      let { username, age, userType, sort, email } = user
      /* db.run(`INSERT INTO user VALUES ( NULL, '${username}','${age}', '${userType}', '${email}', '${sort}' )`, (err) => {
         if (err) return console.log(err)
         console.log('插入数据成功')
       }) */
      db.run(`INSERT INTO user VALUES ( NULL, ?, ?, ?, ?, ? )`, [username, age, userType, email, sort], (err) => {
        if (err) return reject(err)
        resolve('添加数据成功')
      })
    })
  }

  // addUser({ username: 'kunkun', age: 18, userType: 'user', email: 'kunkun@qq.com', sort: 10 })
  // addUser({ username: '唔西迪西', age: 20, userType: 'admin', email: 'wuxidxi@qq.com', sort: 12 })

  // 删除数据
  function delUser(id: number) {
    return new Promise((resolve, reject) => {
      db.run(`DELETE FROM user WHERE id = ${id}`, (err) => {
        if (err) return reject(err)
        resolve('删除数据成功')
      })
    })
  }
  // delUser(2)

  // 修改数据
  function updateUser(id: number, user: Partial<Omit<User, 'id'>>) {
    return new Promise((resolve, reject) => {
      let arr = []
      for (let key in user) {
        let value = `${key} = '${user[key as keyof Omit<User, 'id'>]}'`
        arr.push(value)
      }
      if (!arr.length) return reject('没有要修改的数据')

      db.run(`UPDATE user SET ${arr.join(',')} WHERE id = ${id}`, (err) => {
        if (err) return console.log(err)
        resolve('修改数据成功')
      })
    })
  }

  // updateUser(1, { username: '坤坤', userType: 'admin' })

  // 获取用户数据
  function getUser(id: number): Promise<User> {
    return new Promise((resolve, reject) => {
      db.get(`SELECT * FROM user WHERE id = ${id}`, (err: Error, row: User) => {
        if (err) return reject(err)
        resolve(row)
      })
    })
  }

  // 获取用户列表数据
  function getUserList(): Promise<User[]> {
    return new Promise((resolve, reject) => {
      db.all(`SELECT * FROM user`, (err: Error, rows: User[]) => {
        if (err) return reject(err)
        resolve(rows)
      })
    })
  }

  // addUser({ id: 1, username: 'kunkun', age: 18, userType: 'user', email: 'kunkun1@qq.com', sort: 10 })
</script>

<style scoped></style>
